/*
 * Solo - A small and beautiful blogging system written in Java.
 * Copyright (c) 2010-present, b3log.org
 *
 * Solo is licensed under Mulan PSL v2.
 * You can use this software according to the terms and conditions of the Mulan PSL v2.
 * You may obtain a copy of Mulan PSL v2 at:
 *         http://license.coscl.org.cn/MulanPSL2
 * THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT, MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
 * See the Mulan PSL v2 for more details.
 */
package org.b3log.solo.repository;

import lombok.SneakyThrows;
import lombok.val;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.io.FileUtils;
import org.apache.logging.log4j.Level;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.b3log.latke.Keys;
import org.b3log.latke.repository.*;
import org.b3log.latke.repository.annotation.Repository;
import org.b3log.latke.repository.jdbc.JdbcTransaction;
import org.b3log.latke.repository.jdbc.util.JdbcUtil;
import org.json.JSONObject;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.stream.Collectors;

import static org.b3log.latke.repository.jdbc.JdbcRepository.TX;
import static org.b3log.solo.processor.FetchUploadProcessor.BASE_PATH;


/**
 * @Author LiGuanglei
 * @Date 2023/9/28 16:42
 * @Description 附件管理
 */
@Repository
public class FileRepository extends AbstractRepository {

    private static final Logger LOGGER = LogManager.getLogger(FileRepository.class);

    /**
     * Public constructor.
     */
    public FileRepository() {
        super("files");
    }

    /**
     * @Author LiGuanglei
     * @Date 2023/9/28 16:58
     * @Description 通过时间戳删除
     */
    public void removeByTimestamp(final String time) throws RepositoryException {
        super.remove(time);
    }

    @Override
    public void update(final String id, final JSONObject user, final String... propertyNames) throws RepositoryException {
        super.update(id, user, propertyNames);

        user.put(Keys.OBJECT_ID, id);
    }


    public JSONObject getByMd5(String md5Hex) {
        JSONObject fileBean = null;
        try {
            fileBean = super.get(
                    new Query().setFilter(new PropertyFilter("md5", FilterOperator.EQUAL, md5Hex)).setPageCount(1)
            );
            // (ArrayList) speedFile.get("rslts")
            ArrayList rslts = (ArrayList) fileBean.get("rslts");
            if (rslts.size() == 0) {
                return null;
            }
            fileBean = (JSONObject) rslts.get(0);
        } catch (RepositoryException e) {
            throw new RuntimeException(e);
        }

        return fileBean;
    }

    public void updateByArticleId(List<String[]> f, String aid) throws Exception {
        if (f != null && f.isEmpty()) {
            return;
        }

        final JdbcTransaction currentTransaction = TX.get();
        if (null == currentTransaction) {
            throw new RuntimeException("Invoking update() outside a transaction");
        }
        final Connection connection = currentTransaction.getConnection();

        // 检查图片在不在
        StringBuilder querySql = new StringBuilder();
        String collectUrl = f.stream().map(m -> m[0]).collect(Collectors.joining("','"));
        querySql.append("select * from b3_solo_files where url in ('").append(collectUrl).append("')");
        List<JSONObject> b3SoloFiles = JdbcUtil.queryListJson(querySql.toString(), Collections.emptyList(), connection, getName(), true);

        ArrayList<String[]> install = new ArrayList<>();
        ArrayList<String[]> update = new ArrayList<>();
        if (b3SoloFiles.isEmpty()) {
            install.addAll(f);
        }
        b3SoloFiles.forEach(l -> {
            String url = l.getString("url");
            String[] orig = f.stream().filter(f1 -> f1[0].equals(url)).findFirst().get();
            if (url == null) {
                // 没有？新增
                install.add(orig);
            } else if (orig[1].equals(l.getString("filename")) && aid.equals(l.getString("articleId"))) {
                // 不需要更新的也不更新
            } else {
                update.add(orig);
            }
        });


        if (!install.isEmpty()) {
            install.forEach(e -> {
                String url = e[0];
                File file = new File(BASE_PATH + url);
                if (file.exists()) {
                    try {
                        FileInputStream data = new FileInputStream(file);
                        String md5Hex = DigestUtils.md5Hex(data);
                        data.close();
                        String suffix = url.substring(url.lastIndexOf(".") + 1);
                        JSONObject byMd5 = getByMd5(md5Hex);

                        if (byMd5 == null) {
                            // 存入数据库
                            JSONObject fileBean = new JSONObject();
                            fileBean.put("path", file.getCanonicalPath());
                            fileBean.put("url", url);
                            fileBean.put("suffer", suffix);
                            fileBean.put("filename", e[1]);
                            fileBean.put("createTime", LocalDateTime.now());

                            fileBean.put("articleId", aid);
                            fileBean.put("md5", md5Hex);

                            addNewFile(fileBean);

                        } else {
                            // 只更新数据
                            byMd5.put("filename", e[1]);
                            byMd5.put("url", url);
                            byMd5.put("createTime", LocalDateTime.now());
                            byMd5.put("articleId", aid);
                            byMd5.remove("oId");
                            addNewFile(byMd5);

                        }

                    } catch (IOException ex) {
                        throw new RuntimeException(ex);
                    }
                }
            });

        }

        if (update.isEmpty()) {
            // 不需要更新的结束
            return;
        }
        try {

            final List<Object> paramList = new ArrayList<>();
            final StringBuilder sql = new StringBuilder();

            sql.append("UPDATE b3_solo_files SET articleId = ?,filename = CASE url ");

            for (String[] file : update) {
                sql.append(" WHEN '").append(file[0]).append("' THEN '").append(file[1]).append("' ");
            }
            sql.append(" END ");

            sql.append("where url in ('").append(update.stream().map(m -> m[0]).collect(Collectors.joining("','"))).append("') ");
//                    .append("and articleId like 'last_%'");

            paramList.add(aid);

            JdbcUtil.executeSql(sql.toString(), paramList, connection, true);
        } catch (final Exception e) {
            LOGGER.log(Level.ERROR, "Updates a record [id=" + aid + "] failed", e);
            throw new RuntimeException(e);
        }
    }

    public JSONObject getByUrl(String url) {
        JSONObject fileBean = null;
        try {
            fileBean = super.get(
                    new Query().setFilter(new PropertyFilter("url", FilterOperator.EQUAL, url)).setPageSize(1)
            );
            ArrayList<JSONObject> rslts = (ArrayList) fileBean.get("rslts");

            return rslts.isEmpty() ? new JSONObject() : rslts.get(0);
        } catch (RepositoryException e) {
            throw new RuntimeException(e);
        }
    }


    public void deleteByArticleId(String articleId) {
        final JdbcTransaction currentTransaction = TX.get();
        if (null == currentTransaction) {
            throw new RuntimeException("Invoking update() outside a transaction");
        }

        try {
            final Connection connection = currentTransaction.getConnection();

            //todo 查出文件
            List<JSONObject> jsonObjects = JdbcUtil.queryListJson("select * from b3_solo_files where articleId = ?", List.of(articleId), connection, getName(), true);
            //todo 删除文件
            jsonObjects.forEach(e -> {
                if (e.getInt("count") == 1) {
                    File file = new File(e.getString("path"));
                    // 删除文件
                    boolean b = file.isFile() && file.delete();
                    // 更新数量
                    try {
//                        String sql = "delete from b3_solo_files where oId = ?;" +
//                                "update b3_solo_files set count = (select * from (select count(1) from b3_solo_files where md5 = ?) t) where md5 = ?;";
//                        JdbcUtil.executeSql(sql, List.of(e.get("oId"), e.get("md5"), e.get("md5")), connection, true);

                        JdbcUtil.executeSql("delete from b3_solo_files where articleId = '?'", List.of(e.get("oId")), connection, true);
                        JdbcUtil.executeSql("update b3_solo_files set count = (select * from (select count(1) from b3_solo_files where md5 = '?') t) where md5 = '?';", List.of(e.get("md5"), e.get("md5")), connection, true);
                    } catch (SQLException ex) {
                        throw new RuntimeException(ex);
                    }

                }
            });


        } catch (final Exception e) {
            LOGGER.log(Level.ERROR, "delete a record [id=" + articleId + "] failed", e);
            throw new RuntimeException(e);
        }

    }

    public List<JSONObject> getOutTimeList() {

        final JdbcTransaction currentTransaction = TX.get();
        if (null == currentTransaction) {
            throw new RuntimeException("Invoking update() outside a transaction");
        }

        try {
            final Connection connection = currentTransaction.getConnection();
            final List<Object> paramList = new ArrayList<>();
            final StringBuilder sql = new StringBuilder();

            sql.append(" select oId,right(articleId,10) times from b3_solo_files where articleId like 'last_%' ");

            List<JSONObject> jsonObjects = JdbcUtil.queryListJson(sql.toString(), paramList, connection, getName(), true);
            return jsonObjects;
        } catch (final Exception e) {
            LOGGER.log(Level.ERROR, "a record failed", e);
            throw new RuntimeException(e);
        }
    }

    @SneakyThrows
    public boolean executeSql(String sql) {
        final JdbcTransaction currentTransaction = TX.get();
        if (null == currentTransaction) {
            throw new RuntimeException("SQL 异常！！");
        }

        boolean b = JdbcUtil.executeSql(sql, currentTransaction.getConnection(), true);
        return b;
    }

    @SneakyThrows
    public boolean executeQuery(String sql) {
//        final JdbcTransaction currentTransaction = TX.get();
//        if (null == currentTransaction) {
//            throw new RuntimeException("SQL 异常！！");
//        }
//
//        boolean b = JdbcUtil.queryListJson(sql, Collectors.,currentTransaction.getConnection(), true);
        return false;
    }

    @SneakyThrows
    public void addNewFile(JSONObject fj) {
        super.add(fj);
        final JdbcTransaction conn = TX.get();
        if (null == conn) {
            throw new RuntimeException("Invoking update() outside a transaction");
        }
        JdbcUtil.executeSql("update b3_solo_files set count = (select * from (select count(1) from b3_solo_files where md5 = ?) t) where md5 = ?", List.of(fj.get("md5"), fj.get("md5")), conn.getConnection(), true);

    }

    public void setDelTags(List<String> collect, String aid) {
        final JdbcTransaction connection = TX.get();
        if (null == connection) {
            throw new RuntimeException("Invoking update() outside a transaction");
        }

        final StringBuilder sql = new StringBuilder();

        sql.append("UPDATE b3_solo_files SET articleId = ?  ");
        sql.append("where url in ('").append(collect.stream().collect(Collectors.joining("','"))).append("') ");

        try {
            JdbcUtil.executeSql(sql.toString(), List.of("del_" + aid), connection.getConnection(), true);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
}
